package com.example.project;

import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteException;
import android.database.sqlite.SQLiteOpenHelper;

public class MySqlite extends SQLiteOpenHelper {

	private static final int DB_VERSION    = 1;
	   private static String DB_PATH        = "/data/data/com.example.project/databases/";
	   private static String DB_NAME         = "beyond.db";
	   private static String ASSETS_NAME     = "beyond.db";

	   private final Context myContext;
	   
	   public MySqlite (Context context) {
	       super(context, DB_PATH + DB_NAME,null,DB_VERSION);
	       this.myContext = context;
	   }
	   
	   public void createDataBase() throws IOException{
	       boolean dbExist = checkDataBase();
	       if(dbExist)
	       {
	          
	       }
	       else
	       {
	               File dir = new File(DB_PATH);
	               if(!dir.exists()){
	                   dir.mkdirs();
	               }
	               File dbf = new File(DB_PATH + DB_NAME);
	               if(dbf.exists()){
	                   dbf.delete();
	               }
	               SQLiteDatabase.openOrCreateDatabase(dbf, null);
	               
	               copyDataBase();
	       }
      }
	   
	   private boolean checkDataBase(){
	       SQLiteDatabase checkDB = null;
	       String myPath = DB_PATH + DB_NAME;
	       try{            
	           checkDB = SQLiteDatabase.openDatabase(myPath, null, SQLiteDatabase.OPEN_READONLY);
	       }catch(SQLiteException e){
	          
	       }
	       if(checkDB != null){
	           checkDB.close();
	       }
	        return checkDB != null ? true : false;
	   }

	   private void copyDataBase() throws IOException{
	       
	       InputStream myInput = myContext.getAssets().open(ASSETS_NAME);
	       String outFileName = DB_PATH + DB_NAME;
	       OutputStream myOutput = new FileOutputStream(outFileName);
	       byte[] buffer = new byte[1024];
	       int length;
	       while ((length = myInput.read(buffer))>0){
	           myOutput.write(buffer, 0, length);
	       }
	       myOutput.flush();
	       myOutput.close();
	       myInput.close();
	   }
	   
	   
	   
	   
	   
	   
	   
	   
	   public static final String TABLE_NAME = "LIDONGYANG";
	   public static final String TI = "Title";
	   public static final String AR = "Article";;
	   public static final String MY = "MyLove";
	   public static final String CO = "Company";
	   public static final String CS = "CompanySign";
	   public static final String PO = "Position";
	   public static final String PS = "PositionSign";
	   public static final String SK = "Skill";
	   public static final String SS = "SkillSign";
	   
	   
	   
	   public String[] getList()
	   { 
		   int i=0;
		   SQLiteDatabase db;
		   db=this.getReadableDatabase();
		   Cursor result=db.query(TABLE_NAME,new String[] {TI}, null , null, null, null, null);
		   int count=result.getCount();
		   String[] H=new String[count];
		   while (result.moveToNext())
		   {
			   H[i]=result.getString(result.getColumnIndex(TI));
			   i++;
		   }
		   db.close();
		   return H;
	
	   }
	   
	   
	   public String[] getTaglist(int key)
	   { 
		   int i=0;
		   String[] T=new String[] {"1"};
		   SQLiteDatabase db;
		   db=this.getReadableDatabase();
		   
		   Cursor result;
		   if (key==1)
			   result=db.query(TABLE_NAME,new String[] {TI,CS},CS+"=?",T, null, null, null);
		   else if (key==2)
			   result=db.query(TABLE_NAME,new String[] {TI,PS},PS+"=?",T, null, null, null);
		   else
			   result=db.query(TABLE_NAME,new String[] {TI,SS},SS+"=?",T, null, null, null);
		   int count=result.getCount();
		   String[] H=new String[count];
		   while (result.moveToNext())
		   {
			   H[i]=result.getString(result.getColumnIndex(TI));
			   i++;
		   }
		   db.close();
		   return H;
	   }
	   
	   
	   
	   public String getData(String title)
	   { 
		   String[] T=new String[] {title};
		   SQLiteDatabase db;
		   db=this.getReadableDatabase();
		   
		   Cursor result;
		   result=db.query(TABLE_NAME,new String[] {TI,AR},TI+"=?",T, null, null, null);
		   int count=result.getCount();
		   if (count==0) return null;
		   result.moveToFirst();
		   String H;
		   H=result.getString(result.getColumnIndex(AR));
		   db.close();
		   return H;
	   }
	   
	   public boolean addMylist(String title)
	   { 
		   String[] T=new String[] {title};
		   SQLiteDatabase db;
		   db=this.getReadableDatabase();
		   
		   Cursor result;
		   result=db.query(TABLE_NAME,new String[] {TI,AR,MY,CO,CS,PO,PS,SK,SS},TI+"=?",T, null, null, null);
		   int count=result.getCount();
		   if (count==0) return false;
		   result.moveToFirst();
		  
		   ContentValues NEW=new ContentValues();
		   NEW.put(TI, result.getString(result.getColumnIndex(TI)));
		   NEW.put(AR, result.getString(result.getColumnIndex(AR)));
		   NEW.put(MY, "1");
		   NEW.put(CO, result.getString(result.getColumnIndex(CO)));
		   NEW.put(CS, result.getString(result.getColumnIndex(CS)));
		   NEW.put(PO, result.getString(result.getColumnIndex(PO)));
		   NEW.put(PS, result.getString(result.getColumnIndex(PS)));
		   NEW.put(SK, result.getString(result.getColumnIndex(SK)));
		   NEW.put(SS, result.getString(result.getColumnIndex(SS)));		  
		   db.update(TABLE_NAME, NEW, TI+"=?", T);
		   
		   db.close();
		   return true;
	   }
	   
	   
	   
	   public boolean daleteMylist(String title)
	   { 
		   String[] T=new String[] {title};
		   SQLiteDatabase db;
		   db=this.getReadableDatabase();
		   
		   Cursor result;
		   result=db.query(TABLE_NAME,new String[] {TI,AR,MY,CO,CS,PO,PS,SK,SS},TI+"=?",T, null, null, null);
		   int count=result.getCount();
		   if (count==0) return false;
		   result.moveToFirst();
		  
		   ContentValues NEW=new ContentValues();
		   NEW.put(TI, result.getString(result.getColumnIndex(TI)));
		   NEW.put(AR, result.getString(result.getColumnIndex(AR)));
		   NEW.put(MY, "0");
		   NEW.put(CO, result.getString(result.getColumnIndex(CO)));
		   NEW.put(CS, result.getString(result.getColumnIndex(CS)));
		   NEW.put(PO, result.getString(result.getColumnIndex(PO)));
		   NEW.put(PS, result.getString(result.getColumnIndex(PS)));
		   NEW.put(SK, result.getString(result.getColumnIndex(SK)));
		   NEW.put(SS, result.getString(result.getColumnIndex(SS)));
		   db.update(TABLE_NAME, NEW, TI+"=?", T);
		   
		   db.close();
		   return true;
	   }
	   
	   
	   
	   public String[] getMylist()
	   { 
		   int i=0;
		   String[] T=new String[] {"1"};
		   SQLiteDatabase db;
		   db=this.getReadableDatabase();
		   Cursor result=db.query(TABLE_NAME,new String[] {TI,MY},MY+"=?" ,T, null, null, null);
		   int count=result.getCount();
		   String[] H=new String[count];
		   while (result.moveToNext())
		   {
			   H[i]=result.getString(result.getColumnIndex(TI));
			   i++;
		   }
		   db.close();
		   return H;
	   }
	   
	   public String[] Search(String name)
	   {
		   int i=0;
		   String[] T=new String[] {"1"};
		   SQLiteDatabase db;
		   db=this.getReadableDatabase();
		   Cursor result=db.query(TABLE_NAME,new String[] {TI}, null, null, null, null, null);
		   int count=result.getCount();
		   String[] H=new String[count];
		   String temp="";
		   while (result.moveToNext())
		   {
			   temp=result.getString(result.getColumnIndex(TI));
			   
			   if (temp.indexOf(name)!=-1) 
			   {
				   H[i]=temp;
				   i++;
			   }
		   }
		   
		   String[] L=new String[i];
		   for (int j=0;j<i;j++)
		   {
			   L[j]=H[j];
		   }
		   db.close();
		   return L;
	   }
	   
	   @Override
	   public void onCreate(SQLiteDatabase db) 
	   {
		   
	   }
	  
	   @Override
	   public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion)
	   {
	      
	   }
}